package com.studyroom.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.studyroom.entity.Reservation;
import com.studyroom.dto.SearchDTO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Result;
import java.util.List;
import java.util.Map;
import java.time.LocalDateTime;

@Mapper
public interface ReservationMapper extends BaseMapper<Reservation> {

    @Select("SELECT COUNT(*) FROM reservations WHERE DATE(created_at) = CURDATE()")
    Long countTodayReservations();

    @Select("SELECT r.*, u.username, sr.name as roomName, " +
            "DATE_FORMAT(r.start_time, '%Y-%m-%d %H:%i') as startTime, " +
            "s.seat_number as seatNumber " +
            "FROM reservations r " +
            "LEFT JOIN users u ON r.user_id = u.id " +
            "LEFT JOIN study_rooms sr ON r.room_id = sr.id " +
            "LEFT JOIN seats s ON r.seat_id = s.id " +
            "ORDER BY r.created_at DESC LIMIT 5")
    List<Map<String, Object>> selectLatestReservations();

    @Select("WITH RECURSIVE dates AS (" +
            "  SELECT CURDATE() - INTERVAL 6 DAY as date " +
            "  UNION ALL " +
            "  SELECT date + INTERVAL 1 DAY " +
            "  FROM dates " +
            "  WHERE date < CURDATE()" +
            ") " +
            "SELECT DATE_FORMAT(d.date, '%m-%d') as date, " +
            "COUNT(r.id) as value " +
            "FROM dates d " +
            "LEFT JOIN reservations r ON DATE(r.created_at) = d.date " +
            "GROUP BY d.date " +
            "ORDER BY d.date")
    List<Map<String, Object>> selectReservationStatistics();

    @Select("<script>" +
            "SELECT r.*, u.username, sr.name as roomName, s.seat_number as seatNumber, " +
            "DATE_FORMAT(r.start_time, '%Y-%m-%d %H:%i:%s') as startTime, " +
            "DATE_FORMAT(r.end_time, '%Y-%m-%d %H:%i:%s') as endTime, " +
            "DATE_FORMAT(r.created_at, '%Y-%m-%d %H:%i:%s') as createdAt " +
            "FROM reservations r " +
            "LEFT JOIN users u ON r.user_id = u.id " +
            "LEFT JOIN study_rooms sr ON r.room_id = sr.id " +
            "LEFT JOIN seats s ON r.seat_id = s.id " +
            "WHERE 1=1 " +
            "<if test='roomId != null'>" +
            "  AND r.room_id = #{roomId} " +
            "</if>" +
            "<if test='startTime != null'>" +
            "  AND r.start_time &gt;= #{startTime} " +
            "</if>" +
            "<if test='endTime != null'>" +
            "  AND r.end_time &lt;= #{endTime} " +
            "</if>" +
            "ORDER BY r.created_at DESC" +
            "</script>")
    @Results({
        @Result(column = "startTime", property = "startTime"),
        @Result(column = "endTime", property = "endTime"),
        @Result(column = "createdAt", property = "createdAt"),
        @Result(column = "roomName", property = "roomName"),
        @Result(column = "seatNumber", property = "seatNumber")
    })
    IPage<Reservation> searchReservations(Page<Reservation> page, 
                                         @Param("roomId") Long roomId,
                                         @Param("startTime") LocalDateTime startTime,
                                         @Param("endTime") LocalDateTime endTime);

    @Select({
        "<script>",
        "SELECT r.*, u.username, sr.name as roomName, s.seat_number as seatNumber, " +
        "DATE_FORMAT(r.start_time, '%Y-%m-%d %H:%i:%s') as startTime, " +
        "DATE_FORMAT(r.end_time, '%Y-%m-%d %H:%i:%s') as endTime, " +
        "DATE_FORMAT(r.created_at, '%Y-%m-%d %H:%i:%s') as createdAt " +
        "FROM reservations r " +
        "LEFT JOIN users u ON r.user_id = u.id " +
        "LEFT JOIN study_rooms sr ON r.room_id = sr.id " +
        "LEFT JOIN seats s ON r.seat_id = s.id " +
        "WHERE r.user_id = #{userId} " +
        "<if test='status != null'> " +
        "  AND r.status = #{status} " +
        "</if> " +
        "ORDER BY r.created_at DESC",
        "</script>"
    })
    @org.apache.ibatis.annotations.Results({
        @org.apache.ibatis.annotations.Result(column = "startTime", property = "startTime"),
        @org.apache.ibatis.annotations.Result(column = "endTime", property = "endTime"),
        @org.apache.ibatis.annotations.Result(column = "createdAt", property = "createdAt")
    })
    Page<Reservation> getMyReservations(
        @Param("page") Page<Reservation> page, 
        @Param("userId") Long userId, 
        @Param("status") Integer status
    );

    @Select("SELECT r.*, " +
            "u.username, " +
            "sr.name as room_name, " +
            "s.seat_number " +
            "FROM reservations r " +
            "LEFT JOIN users u ON r.user_id = u.id " +
            "LEFT JOIN study_rooms sr ON r.room_id = sr.id " +
            "LEFT JOIN seats s ON r.seat_id = s.id " +
            "${ew.customSqlSegment}")
    IPage<Reservation> selectReservationPage(Page<Reservation> page, @Param("ew") QueryWrapper<Reservation> wrapper);
} 